/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.study.dao;

import com.study.util.DButil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import com.study.dto.CoursesDTO;

/**
 *
 * @author Truong Anh Tuan
 */
public class CoursesDAO {

    public CoursesDAO() {
    }
    Connection con = null;
    PreparedStatement pst = null;
    private final String SQL_READ_BY_ALL = "SELECT * FROM COURSES";
    private final String SQL_READ_BY_ID = "SELECT * FROM COURSES WHERE COURSE_ID = ?";
    private final String SQL_CREATE = "INSERT INTO COURSES(COURSE_ID, COURSE_NAME, FEE, MAX_LIMIT, CURR_ENROLL) " +
            "VALUES(?, ?, ?, ?, ?)";
    private final String SQL_UPDATE = "UPDATE COURSES set COURSE_NAME = ?, FEE = ?, " +
            "MAX_LIMIT = ?, CURR_ENROLL =? WHERE COURSE_ID = ?";
    private final String SQL_DELETE = "DELETE FROM COURSES WHERE COURSE_ID = ?";

    public ArrayList<CoursesDTO> ReadByAll() {
        try {
            ArrayList<CoursesDTO> courses = new ArrayList<CoursesDTO>();
            con = DButil.GetConnection();
            pst = con.prepareStatement(SQL_READ_BY_ALL);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                CoursesDTO dto = new CoursesDTO();
                dto.setCoursesID(rs.getString(1));
                dto.setCoursesName(rs.getString(2));
                dto.setFee(rs.getInt(3));
                dto.setMaxLimit(rs.getInt(4));
                dto.setCurrEnroll(rs.getInt(5));
                courses.add(dto);
            }
            return courses;
        } catch (SQLException ex) {
            Logger.getLogger(CoursesDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            DButil.closeConnection(con);
        }

    }

    public CoursesDTO ReadById(String id) {
        try {
            CoursesDTO dto = new CoursesDTO();
            con = DButil.GetConnection();
            pst = con.prepareStatement(SQL_READ_BY_ID);
            pst.setString(1, id);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {

                dto.setCoursesID(rs.getString(1));
                dto.setCoursesName(rs.getString(2));
                dto.setFee(rs.getInt(3));
                dto.setMaxLimit(rs.getInt(4));
                dto.setCurrEnroll(rs.getInt(5));

            }
            return dto;
        } catch (SQLException ex) {
            Logger.getLogger(CoursesDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            DButil.closeConnection(con);
        }

    }

    public boolean Create(CoursesDTO dto) {
        try {
            con = DButil.GetConnection();
            pst = con.prepareStatement(SQL_CREATE);
            pst.setString(1, dto.getCoursesID());
            pst.setString(2, dto.getCoursesName());
            pst.setInt(3, dto.getFee());
            pst.setInt(4, dto.getMaxLimit());
            pst.setInt(5, dto.getCurrEnroll());
            if (pst.executeUpdate() > 0) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            Logger.getLogger(CoursesDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } finally {
            DButil.closeConnection(con);
        }

    }

    public boolean Update(CoursesDTO dto) {
        try {
            con = DButil.GetConnection();
            pst = con.prepareStatement(SQL_UPDATE);
            pst.setString(1, dto.getCoursesName());
            pst.setInt(2, dto.getFee());
            pst.setInt(3, dto.getMaxLimit());
            pst.setInt(4, dto.getCurrEnroll());
            pst.setString(5, dto.getCoursesID());
            if (pst.executeUpdate() > 0) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            Logger.getLogger(CoursesDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } finally {
            DButil.closeConnection(con);
        }
    }

     public boolean Delete(String id) {
        try {
            con = DButil.GetConnection();
            pst = con.prepareStatement(SQL_DELETE);
            pst.setString(1,id);
            
            if (pst.executeUpdate() > 0) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            Logger.getLogger(CoursesDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } finally {
            DButil.closeConnection(con);
        }
    }
}
